package com.trace.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import com.trace.dao.BorrowBookDao;
import com.trace.entity.BorrowBook;

public class BorrowBookDaoImpl implements BorrowBookDao {

	public boolean insertBorrowBook(BorrowBook borrowBook) {
		StringBuffer sql = new StringBuffer();
		sql.append("insert into borrow_book (book_id,customer_id,borrow_date,end_date) values (");
		sql.append(" " + borrowBook.getBookId() + ",");
		sql.append(" " + borrowBook.getCustomerId() + ",");
		sql.append(" now() ,");
		sql.append(" DATE_ADD(now(),INTERVAL 2 Month) ");
		sql.append(")");
		Connection conn = null;
		ResultSet rs = null;
		Statement stmt = null;
		try {
			conn = DBUtils.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			stmt.executeUpdate(sql.toString());

			System.out.println("=======insert borrow book");
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			DBUtils.free(rs, stmt, conn);
		}
	}

	public List<BorrowBook> findBorrowBookByCondition(BorrowBook paramBorrowBook) {
		String sql = "select book.Book_Id,book.book_name,borrow.customer_id,borrow.borrow_date,borrow.end_date";
		sql = sql
				+ " ,borrow.borrow_Id ,borrow.borrow_Memo,borrow.return_Date from book book,borrow_book borrow ";
		sql = sql
				+ " where book.book_id=borrow.book_id and borrow.customer_id = "
				+ paramBorrowBook.getCustomerId();
		sql = sql + " order by borrow.return_date ASC ";
		Connection conn = null;
		ResultSet rs = null;
		Statement stmt = null;
		BorrowBook borrowBook = null;
		List<BorrowBook> listBorrowBook = null;
		try {
			conn = DBUtils.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			rs = stmt.executeQuery(sql);

			if (rs != null) {
				listBorrowBook = new LinkedList<BorrowBook>();
			} else {
				return null;
			}
			while (rs.next()) {
				borrowBook = new BorrowBook();
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
					borrowBook.setBorrowId(rs.getInt("borrow_Id"));
					borrowBook.setBookId(rs.getInt("book_Id"));
					borrowBook.setBookName(rs.getString("book_Name"));
					borrowBook.setCustomerId(rs.getInt("customer_Id"));
					borrowBook.setBorrowDate(rs.getDate("borrow_Date"));
					borrowBook.setEndDate(rs.getDate("end_Date"));
					borrowBook.setReturnDate(rs.getDate("return_Date"));
					borrowBook.setBorrowMemo(rs.getString("borrow_Memo"));

				}
				listBorrowBook.add(borrowBook);
			}
			System.out.println("=======listBorrowBook count:"
					+ listBorrowBook.size());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.free(rs, stmt, conn);
		}
		return listBorrowBook;
	}

	public boolean updateBorrowBook(BorrowBook borrowBook) {
		StringBuffer sql = new StringBuffer();
		sql.append("update borrow_book set return_date = now()");
		sql.append(" where borrow_id = " + borrowBook.getBorrowId());
		Connection conn = null;
		ResultSet rs = null;
		Statement stmt = null;
		try {
			conn = DBUtils.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			stmt.executeUpdate(sql.toString());

			System.out.println("=======update bbook count");
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			DBUtils.free(rs, stmt, conn);
		}
	}

	public BorrowBook findBorrowBookByID(BorrowBook paramBorrowBook) {
		String sql = "select book.Book_Id,book.book_name,borrow.customer_id,borrow.borrow_date,borrow.end_date";
		sql = sql
				+ " ,borrow.borrow_Id ,borrow.borrow_Memo,borrow.return_Date from book book,borrow_book borrow ";
		sql = sql
				+ " where book.book_id=borrow.book_id and borrow.borrow_id = "
				+ paramBorrowBook.getBorrowId();
		sql = sql + " order by borrow.return_date ASC ,borrow.borrow_id DESC ";

		System.out.println("===sql" + sql);
		Connection conn = null;
		ResultSet rs = null;
		Statement stmt = null;
		BorrowBook borrowBook = null;
		try {
			conn = DBUtils.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				borrowBook = new BorrowBook();
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
					borrowBook.setBorrowId(rs.getInt("borrow_Id"));
					borrowBook.setBookId(rs.getInt("book_Id"));
					borrowBook.setBookName(rs.getString("book_Name"));
					borrowBook.setCustomerId(rs.getInt("customer_Id"));
					borrowBook.setBorrowDate(rs.getDate("borrow_Date"));
					borrowBook.setEndDate(rs.getDate("end_Date"));
					borrowBook.setReturnDate(rs.getDate("return_Date"));
					borrowBook.setBorrowMemo(rs.getString("borrow_Memo"));

				}
			}
			System.out.println("=======borrowBook count:");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.free(rs, stmt, conn);
		}
		return borrowBook;
	}

	public int countBorrowBook(BorrowBook paramBorrowBook) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) as borrowCount from borrow_book borrow ");
		sql.append(" where borrow.customer_id = "
				+ paramBorrowBook.getCustomerId());
		sql.append(" and borrow.return_date is null");
		Connection conn = null;
		ResultSet rs = null;
		Statement stmt = null;
		int bookCount = -1;
		try {
			conn = DBUtils.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			rs = stmt.executeQuery(sql.toString());
			while (rs.next()) {
				bookCount = rs.getInt("borrowCount");
			}
			return bookCount;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		} finally {
			DBUtils.free(rs, stmt, conn);
		}

	}

}
